package sw6.admin.database;

import sw6.lib.exceptions.SettingNotFoundException;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	private static final String TBL_BOOLEANS	= "booleans";
	private static final String TBL_OBJECTS		= "objects";
	private static final String TBL_INTEGERS 	= "integers";
	private static final String TBL_STRINGS 	= "strings";
	private static final String COL_APP_NAME	= "app_name";
	private static final String COL_VAR_NAME	= "var_name";
	private static final String COL_VAR_VALUE	= "var_value";
	private static final String COL_VAR_CLASS 	= "var_canonical_class_name";
	private static final String DB_NAME         = "db";
	
	public DatabaseHelper(Context context) {
		super(context, DB_NAME, null, 1);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		createTablesIfNotExists(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}
	
    private void createTablesIfNotExists(SQLiteDatabase db) {
    	String[] createTableStatements = {
    			//booleans
    			"CREATE TABLE booleans (" +
	    			"app_name  TEXT," +
	    			"var_name  TEXT," +
	    			"var_value BOOLEAN," +
	    			"CONSTRAINT 'booleans_primary_key' PRIMARY KEY ( app_name, var_name ));",
				//integers
				"CREATE TABLE integers (" +
					"app_name  TEXT," +
					"var_name  TEXT," +
					"var_value INT," +
					"CONSTRAINT 'integers_primary_key' PRIMARY KEY ( app_name, var_name ));",
				//strings
				"CREATE TABLE strings (" +
					"app_name  TEXT," +
					"var_name  TEXT," +
					"var_value TEXT," +
					"CONSTRAINT 'strings_primary_key' PRIMARY KEY ( app_name, var_name ));,",
				//objects
    			"CREATE TABLE objects ( " +
				    "app_name                 TEXT," +
				    "var_name                 TEXT," +
				    "var_value                BLOB," +
				    "var_canonical_class_name TEXT," +
				"CONSTRAINT 'objects_primary_key' PRIMARY KEY ( app_name, var_name, var_canonical_class_name ));"
    	};
    	
    	for (String createTableStatement : createTableStatements) {
    		db.execSQL(createTableStatement);
		}
    }
    
    /* ================== GETTERS ================== */
    public Cursor getBoolean(String appName, String varName) {
    	return getSetting(appName, varName, TBL_BOOLEANS);
    }
    
    public Cursor getInteger(String appName, String varName) {
    	return getSetting(appName, varName, TBL_INTEGERS);
    }
    
    public Cursor getString(String appName, String varName) {
    	return getSetting(appName, varName, TBL_STRINGS);
    }
    
    public Cursor getObject(String appName, String varName, String varCanonicalClassName) {
    	Cursor settingCursor = this.getReadableDatabase().query(
    			TBL_OBJECTS, 					// table to query
    			new String[] { COL_VAR_VALUE }, // value column
    			COL_APP_NAME  + " = ? AND " + 	// where clause
    			COL_VAR_NAME  + " = ? AND " +
    			COL_VAR_CLASS + " = ?",
    			new String[] {appName, varName, varCanonicalClassName}, 
    			null, 							// group by
    			null, 							// having
    			null);							// order by
    	
    	//Check if cursor contains any rows. 
    	// If not, the request for a setting was formed incorrectly.
    	if(settingCursor.moveToFirst() == false) {
    		settingCursor.close();
    	}
    	return settingCursor;
    }
    
    private Cursor getSetting(String appName, String varName, String table) {
    	Cursor settingCursor = this.getReadableDatabase().query(
    			table, 							// table to query
    			new String[] { COL_VAR_VALUE }, // value column
    			COL_APP_NAME + " = ? AND " + 	// where clause
    			COL_VAR_NAME + " = ?", 
    			new String[] {appName, varName}, 
    			null, 							// group by
    			null, 							// having
    			null);							// order by
    	
    	//Check if cursor contains any rows. 
    	// If not, the request for a setting was formed incorrectly.
    	if(settingCursor.moveToFirst() == false) {
    		settingCursor.close();
    	}
    	return settingCursor;
    }
    
    /* ================== UPDATERS ================== */
    public void updateBoolean(String appName, String varName, boolean varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	updateSetting(contentValues, appName, varName, TBL_BOOLEANS);
    }
    
    public void updateInteger(String appName, String varName, int varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	updateSetting(contentValues, appName, varName, TBL_INTEGERS);
    }
    
    public void updateString(String appName, String varName, String varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	updateSetting(contentValues, appName, varName, TBL_STRINGS);
    }
    
    public void updateObject(String appName, String varName, String varCanonicalClassName, byte[] varValue) {
    	
    		ContentValues contentValues = new ContentValues();
    		contentValues.put(COL_VAR_VALUE, varValue);
    		contentValues.put(COL_VAR_CLASS, varCanonicalClassName);
    	try {
    		SQLiteDatabase sqliteDatabase = this.getWritableDatabase();
    		int affectedRows = sqliteDatabase.update(
    				TBL_OBJECTS, 
    				contentValues, 
    				COL_APP_NAME  + " = ? AND " + 
    				COL_VAR_NAME  + " = ? AND " + 
    				COL_VAR_CLASS + " = ?", 
    				new String[] { appName, varName, varCanonicalClassName });
    		sqliteDatabase.close();
    		if(affectedRows == 0) {
    			throw new SettingNotFoundException(appName, varName, varCanonicalClassName);
    		}
    	}
    	catch(SettingNotFoundException e) {								//Temporary fix
    		/* 	The reason we catch this exception is that we do not yet offer an easy 
     	   		opportunity to save new variables into the database. Since we have made
     	   		our code to give an error if the variable does not exist then we do just
     	   		temporary so that if the variable does not exist in the database so it
     	   		is added to the database.

     	   		TODO: Please DELETE this when the XML system works.
    		 */
    		insertSetting(contentValues, appName, varName, TBL_OBJECTS);//Temporary fix
    	}	
    }
    
    private void updateSetting(ContentValues contentValues, String appName, String varName, String table) {
    	try {																//Temporary fix
    		SQLiteDatabase sqliteDatabase = this.getWritableDatabase();
        	int affectedRows = sqliteDatabase.update(
        			table, 
        			contentValues, 
        			COL_APP_NAME + " = ? AND " + 
        			COL_VAR_NAME + " = ?", 
        			new String[] { appName, varName });
        	sqliteDatabase.close();
        	if(affectedRows == 0) {
        		throw new SettingNotFoundException(varName, appName);
        	}    		
    	} catch(SettingNotFoundException e) {								//Temporary fix
    		/* 	The reason we catch this exception is that we do not yet offer an easy 
     	   		opportunity to save new variables into the database. Since we have made
     	   		our code to give an error if the variable does not exist then we do just
     	   		temporary so that if the variable does not exist in the database so it
     	   		is added to the database.
     	   		
     	   		TODO: Please DELETE this when the XML system works.
    		 */
    		insertSetting(contentValues, appName, varName, table);			//Temporary fix
    	}																	//Temporary fix
    	
    }
    
    /* ================== INSERTERS ================== */
    public void insertBoolean(String appName, String varName, boolean varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	insertSetting(contentValues, appName, varName, TBL_BOOLEANS);
    }
    
    public void insertInteger(String appName, String varName, int varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	insertSetting(contentValues, appName, varName, TBL_INTEGERS);
    }
    
    public void insertString(String appName, String varName, String varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_VALUE, varValue);
    	insertSetting(contentValues, appName, varName, TBL_STRINGS);
    }
    
    public void insertObject(String appName, String varName, String varCanonicalClassName, byte[] varValue) {
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(COL_VAR_CLASS, varCanonicalClassName);
    	contentValues.put(COL_VAR_VALUE, varValue);
    	insertSetting(contentValues, appName, varName, TBL_OBJECTS);
    }
    
    private void insertSetting(ContentValues contentValues, String appName, String varName, String table) {
    	contentValues.put(COL_APP_NAME, appName);
    	contentValues.put(COL_VAR_NAME, varName);
    	SQLiteDatabase sqliteDatabase = this.getWritableDatabase();
    	long newRowId = sqliteDatabase.insert(table, null, contentValues);
    	sqliteDatabase.close();
    	
    	if(newRowId == -1) {
    		throw new SQLiteException("An error occurred when trying to insert the variable named: " + varName + ", for app.: " + appName + ", into the database. " +
    				"The setting might already exist.");
    	}
    }
    
    /* ================== DELETERS ================== */
    public void truncateTable(String... tables) {
    	SQLiteDatabase sqliteDatabase = this.getWritableDatabase();
    	
    	//truncate all tables
    	for(String table : tables) {
    		sqliteDatabase.delete(table, null, null);
    	}
    	
    	sqliteDatabase.close();
    }
    
    public void truncateAllTables() {
    	truncateTable(TBL_BOOLEANS, TBL_INTEGERS, TBL_OBJECTS, TBL_STRINGS);
    }
}
